import openpyxl
import  yaml
import pymysql
class YMils:
    def readyaml(self,filename):
        with open(filename, 'r', encoding='utf-8')as f:
            data=yaml.safe_load(f.read())
            data1=yaml.load(f.read(),Loader=yaml.FullLoader)
        print(data1)
    def writeyaml(self,filename,data):
        with open(filename,'a+',encoding='utf-8')as f:
            yaml.dump(data,f,allow_unicode=True)
        print(data)
class DBtuils:
    def __init__(self, host, user, password, database, port=3306, charset="utf8mb4"):
        self.host = host
        self.user = user
        self.password = password
        self.database = database
        self.port = port
        self.charset = charset

    # 查询
    def select(self, sql):
        try:
            conn = pymysql.connect(host=self.host, user=self.user, password=self.password,
                                   database=self.database, port=self.port, charset=self.charset)
            cursor = conn.cursor()
            cursor.execute(sql)
            results = cursor.fetchall()
            cursor.close()
            conn.close()
            print("查询成功！")
            return results
        except Exception as e:
            print("查询失败！", e)

    # 增加
    def insert(self, sql):
        try:
            conn = pymysql.connect(host=self.host,user=self.user,password=self.password,
                                   database=self.database,port=self.port,charset=self.charset)
            cursor = conn.cursor()
            cursor.execute(sql)
            conn.commit()
            cursor.close()
            conn.close()
            print("插入成功！")
        except Exception as e:
            print("插入失败！", e)

    # 修改
    def update(self, sql):
        try:
            conn = pymysql.connect(host=self.host, user=self.user, password=self.password,
                                   database=self.database, port=self.port, charset=self.charset)
            cursor = conn.cursor()
            cursor.execute(sql)
            conn.commit()
            cursor.close()
            conn.close()
            print("修改成功！")
        except Exception as e:
            print("修改失败！", e)

    # 删除
    def delete(self, sql):
        try:
            conn = pymysql.connect(host=self.host, user=self.user, password=self.password,
                                   database=self.database, port=self.port, charset=self.charset)
            cursor = conn.cursor()
            cursor.execute(sql)
            conn.commit()
            cursor.close()
            conn.close()
            print("删除成功！")
        except Exception as e:
            print("删除失败！", e)

class ReadExcel:

    @staticmethod
    def read_excel(filename, sheet_name):

        try:

            wb = openpyxl.load_workbook(filename)
            sheet = wb[sheet_name]
            data = [[sheet.cell(row=i, column=j).value for j in range(1, sheet.max_column + 1)] for i in
                    range(2, sheet.max_row + 1)]
            return data
        except Exception as e:
            return "获取数据失败!", e

    @staticmethod
    def read_excel1(filename, sheet_name):

        try:

            wb = openpyxl.load_workbook(filename)
            sheet = wb[sheet_name]
            data = []
            for i in range(2, sheet.max_row + 1):
                row_data = []
                for j in range(1, sheet.max_column + 1):
                    a = sheet.cell(row=i, column=j).value
                    row_data.append(a)
                data.append(row_data)
            return data
        except Exception as e:
            return "获取失败!", e
